<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Customer extends Model
{
    use HasFactory;

    protected $table = 'customer';

    protected $primaryKey = 'id';

    protected $fillable = ['openid', 'nickname', 'six', 'age', 'weight', 'phone', 'birthday'];

    public $timestamps = false;

    /**
     * The attributes that should be hidden for serialization.
     *
     * @var array<int, string>
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];

    public function getDetail(array $params): array
    {
        $whereSql = self::conditions($params);

        $sql = "
            select
                c.id,
                c.username,
                c.phone,
                c.nickname,
                c.headimg,
                c.no,
                c.qr_code,
                c.bar_code,
                c.login_ip,
                c.login_at,
                c.login_num,
                c.six,
                c.birthday,
                c.birthday_type,
                c.status,
                IFNULL(cap.account_balance, 0) as account_balance,
                IFNULL(cap.point_balance, 0) as point_balance,
                ifnull(cm.count, 0) as market_count,
                c.create_at,
                c.update_at
            from
                customer c
                left join capital cap on cap.customer_id = c.id
                left join (
                    select
                        customer_id,
                        count(*) as count
                    from
                        customer_market
                    where
                        delete_flg = 0
                    group by
                        customer_id
                ) cm on cm.customer_id = c.id
            where
                c.delete_flg = 0
                {$whereSql}
        ";
        return DB::select($sql);
    }

    public static function conditions(array $params): string
    {
        $whereSql = "";

        if (!empty($params['id'])) {
            $whereSql .= "and c.id = '{$params['id']}'";
        }
        if (!empty($params['customer_id'])) {
            $whereSql .= "and c.id = '{$params['customer_id']}'";
        }
        if (!empty($params['status'])) {
            $whereSql .= "and c.status = '{$params['status']}'";
        }

        return $whereSql;
    }
}
